﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

public partial class main_uc_application_main : DAO.ESWebUserControl
{
    public string filename = "Application.aspx";
    protected void Page_Load(object sender, EventArgs e)
    {
        InitializeUI();
        if (!Page.IsPostBack)
        {
            LoadApplications(0);
            ddl_Sortby.Items.Clear();
            ddl_Sortby.Items.Add(new ListItem("Data Newest", "1"));
            ddl_Sortby.Items.Add(new ListItem("Data Oldest", "2"));
            ddl_Sortby.Items.Add(new ListItem("Gold Member", "3"));
            ddl_Sortby.Items.Add(new ListItem("Silver Member", "4"));
            ddl_Sortby.Items.Add(new ListItem("Bronze Member", "5"));
        }
    }

    #region LoadApplications
    private void LoadApplications(int sort)
    {
        int page = 1;
        int page_size = 15;//Records on page
        if (Request.QueryString["page"] != null && Request.QueryString["page"] != "")
            Int32.TryParse(Request.QueryString["page"], out page);
        int start = 1;
        int limit = page_size;
        if (page != 1)
        {
            start = limit * (page - 1)+ 1;
            limit = (limit * (page - 1)) + page_size;
        }

        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_GetApplicationsCount", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                if (Request.Url.Segments[2] != null && Request.Url.Segments[2] != "")
                {
                    filename = Request.Url.Segments[2];
                }

                if (filename.ToLower().ToString() == "myapplications.aspx")
                {
                    long company_id = 0;
                    if (Request.Cookies["texchange"] != null && 
                        Request.Cookies["texchange"]["cid"] != null && 
                        DAO.GlobalSettings.Decrypt(Request.Cookies["texchange"]["cid"].ToString()) != "")
                    {
                        Int64.TryParse(DAO.GlobalSettings.Decrypt(Request.Cookies["texchange"]["cid"].ToString()), out company_id);
                    }
                    cmd.Parameters.Add("@company_id", SqlDbType.BigInt).Value = company_id;
                }

                if (Request.QueryString["typeid"] != null && Request.QueryString["typeid"] != "")
                {
                    int type_transport_id = 0;
                    if (Int32.TryParse(Request.QueryString["typeid"], out type_transport_id))
                    {
                        cmd.Parameters.Add("@type_transport_id", SqlDbType.BigInt).Value = type_transport_id;
                    }
                }
                if (Request.QueryString["offerid"] != null && Request.QueryString["offerid"] != "")
                {
                    int type_offer_id = 0;
                    if (Int32.TryParse(Request.QueryString["offerid"], out type_offer_id))
                    {
                        cmd.Parameters.Add("@type_offer_id", SqlDbType.BigInt).Value = type_offer_id;
                    }
                }
                if (Request.Cookies["texchange"] != null && Request.Cookies["texchange"]["logged"] != null && 
                    DAO.GlobalSettings.Decrypt(Request.Cookies["texchange"]["logged"]) == "true" && 
                    (DAO.GlobalSettings.Decrypt(Request.Cookies["texchange"]["type"].ToString()) == "admin"))
                { 
                    cmd.Parameters.Add("isActive", null); 
                }
                else 
                { 
                    cmd.Parameters.Add("isActive", 1); 
                }
               /* if (Request.QueryString["country"] != null && Request.QueryString["country"] != "")
                {
                    int country_id = 0;
                    if (Int32.TryParse(Request.QueryString["country"], out country_id))
                    {
                        cmd.Parameters.Add("@country_id", SqlDbType.BigInt).Value = country_id;
                    }
                }
                if (Request.QueryString["postcode"] != null && Request.QueryString["postcode"] != "")
                {
                    cmd.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = Request.QueryString["postcode"].ToString();
                }*/

                object total_records = cmd.ExecuteScalar();//total records

                cmd.CommandText = "SP_GetApplications";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@start", SqlDbType.BigInt).Value = start;
                cmd.Parameters.Add("@limit", SqlDbType.BigInt).Value = limit;
                if (sort != 0)
                {
                    string sortby = "id desc";
                    switch (sort)
                    {
                        case 1:
                            sortby = "id desc";
                            break;
                        case 2:
                            sortby = "id asc";
                            break;
                        case 3:
                            sortby = "member_type_id asc";
                        break; 
                        case 4:
                        break;
                        case 5:
                             sortby = "member_type_id desc";
                        break;
                    }

                    cmd.Parameters.Add("@sortby", SqlDbType.NVarChar).Value = sortby;
                }

                SqlDataReader dr = cmd.ExecuteReader();//Execute Reader
                StringBuilder sb;
                DateTime dt;
                bool _new = false;
                string _cssClass;

                while (dr.Read())
                {
                    DateTime.TryParse(dr["created"].ToString(), out dt);
                    sb = new StringBuilder();
                    sb.Append("<div class=\"company\"><div class=\"company_header\">");
                    if (DateTime.Compare(dt, Utilities.GetStartOfLastWeek()) >= 0)
                    {
                        sb.Append("<div class=\"comp_new\"></div>");
                        _new = true;
                    }
                    else {
                        _new = false;
                        //sb.Append("<div style='width:69px;float:left;'>&nbsp;</div>");
                    }
                    string univer_type = "vehicle_type";
                    string vehicle_type = "Truck";
                    string vehicle_type_img = "Truck";
                    switch (dr["type_transport_id"].ToString())
                    {
                        case "164"://Road
                            univer_type = "vehicle_type";
                            vehicle_type = DAO.TextBaseFactory.loadText("truck", this.LangId);
                            vehicle_type_img = DAO.TextBaseFactory.loadText("truck", DAO.Connection.DEFAULT_LANGUAGE);
                            break;
                        case "166"://Rail
                            univer_type = "vehicle_type";
                            vehicle_type = DAO.TextBaseFactory.loadText("train", this.LangId);
                            vehicle_type_img = DAO.TextBaseFactory.loadText("train", DAO.Connection.DEFAULT_LANGUAGE);
                            break;
                        case "167"://Air
                            univer_type = "craft_type";
                            vehicle_type = DAO.TextBaseFactory.loadText("airplane", this.LangId);
                            vehicle_type_img = DAO.TextBaseFactory.loadText("airplane", DAO.Connection.DEFAULT_LANGUAGE);
                            break;
                        case "165"://Sea
                            univer_type = "vessel_type";
                            vehicle_type = DAO.TextBaseFactory.loadText("vessel", this.LangId);
                            vehicle_type_img = DAO.TextBaseFactory.loadText("vessel", DAO.Connection.DEFAULT_LANGUAGE);
                            break;
                    }

                    string placement_type = "Other";
                    using (SqlCommand cmd_placement_type = new SqlCommand("SP_GetTechnicalInfo", conn))
                    {
                        cmd_placement_type.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd_placement_type.Parameters.Add("@app_id", DbType.Int64).Value = dr["id"];
                        cmd_placement_type.Parameters.Add("@type_offer_id", DbType.Int64).Value = dr["type_offer_id"];
                        cmd_placement_type.Parameters.Add("@type_transport_id", DbType.Int64).Value = dr["type_transport_id"];
                        SqlDataReader dr_pacement_type = cmd_placement_type.ExecuteReader();

                        while (dr_pacement_type.Read())
                        {
                            long placement_type_id = 0;
                            if (dr_pacement_type["placement_type_id"] != null)
                            {
                                if (long.TryParse(dr_pacement_type["placement_type_id"].ToString(), out placement_type_id))
                                {
                                    switch (placement_type_id)
                                    {
                                        case 62:
                                            placement_type = "FCL";
                                        break;
                                        case 63:
                                            placement_type = "LCL";
                                        break;
                                        case 64:
                                        default:
                                            placement_type = "Other";
                                        break;
                                    }
                                }
                            }
                        }
                    }

                    _cssClass = _new ? "comp_header_type" : "comp_header_type1";
                    sb.Append("<div class=\"comp_header_name\">" + LoadUniversalType(Int64.Parse( dr["type_transport_id"].ToString())) +
                        " " + LoadUniversalType(Int64.Parse(dr["type_offer_id"].ToString())) + "</div>");
                    if (!_new)
                    {
                        sb.Append("<div style='width:69px;float:left;'>&nbsp;</div>");
                    }
                    switch (dr["member_type_id"].ToString())
                    {
                        case "1":
                            sb.Append("<div class='" + _cssClass + "'>* * * " + DAO.TextBaseFactory.loadText("gold_member", this.LangId) + "</div>");
                            break;
                        case "2":
                            sb.Append("<div class='" + _cssClass + "'>* * " + DAO.TextBaseFactory.loadText("silver_member", this.LangId) + "</div>");
                            break;
                        case "3":
                            sb.Append("<div class='" + _cssClass + "'>* " + DAO.TextBaseFactory.loadText("bronze_member", this.LangId) + "</div>");
                            break;
                        case "0":
                            sb.Append("<div class='" + _cssClass + "'> " + DAO.TextBaseFactory.loadText("_member", this.LangId) + "</div>");
                            break;
                        default:
                            sb.Append("<div class='" + _cssClass + "'> " + DAO.TextBaseFactory.loadText("_member", this.LangId) + "</div>");
                            break;
                    }
                    sb.Append("<span class=\"comp_header_transport\"> | "+placement_type+" | " + vehicle_type +
                        "  |  <img src='images/vehicles/" + vehicle_type_img + ".jpg' /></span>");
                    sb.Append("</div>");
                    sb.Append("<div class=\"company_main\">");
                    

                    long appid = 0;
                    Int64.TryParse(dr["id"].ToString(), out appid);
                    Dictionary<string, ArrayList> LoadLoadingUnloadingData = LoadLoadingUnloadingInfo(appid);
                    string loading_date = "N/A";
                    ArrayList loading_codes;
                    ArrayList loading_countries;
                    ArrayList unloading_countries;
                    ArrayList loading_dates;
                    ArrayList unloading_codes;
                    ArrayList unloading_dates;
                    if(LoadLoadingUnloadingData.TryGetValue("loading_dates",out loading_dates))
                    {
                        if (loading_dates.Count > 0)
                        {
                            for (int i = 0; i < 1; i++)
                            {
                                if (DateTime.TryParse(loading_dates[i].ToString(), out dt))
                                {
                                    loading_date = dt.ToShortDateString() + " ";
                                }
                            }
                        }
                    }
                    string unloading_date = "N/A";
                    if (LoadLoadingUnloadingData.TryGetValue("unloading_dates", out unloading_dates))
                    {
                        if (unloading_dates.Count > 0)
                        {
                            for (int i = 0; i < 1; i++)
                            {
                                if (DateTime.TryParse(unloading_dates[i].ToString(), out dt))
                                {
                                    unloading_date = dt.ToShortDateString() + " ";
                                }
                            }
                        }
                    }

                    bool tender_app = false;
                    if (dr["type_offer_id"].ToString()=="71")//For Tender Application display Opening and Closing Date
                    {
                        tender_app = true;
                        using (SqlCommand cmd_tender = new SqlCommand("SP_GetTechnicalInfo", conn))
                        {
                            cmd_tender.CommandType = System.Data.CommandType.StoredProcedure;
                            cmd_tender.Parameters.Add("@app_id", DbType.Int64).Value = appid;
                            cmd_tender.Parameters.Add("@type_offer_id", DbType.Int64).Value = 71;
                            SqlDataReader dr_tender = cmd_tender.ExecuteReader();
                            
                            while (dr_tender.Read())
                            {
                                //Opening_Date
                                DateTime date_var;
                                loading_date = "N/A";
                                if (DateTime.TryParse(dr_tender["opening_date"].ToString(), out date_var))
                                {
                                    loading_date = date_var.ToShortDateString();
                                }
                                //Cloasing_Date
                                unloading_date = "N/A";
                                if (DateTime.TryParse(dr_tender["closing_date"].ToString(), out date_var))
                                {
                                   unloading_date = date_var.ToShortDateString();
                                }
                            }
                            dr_tender.Close();
                        }  
                    }

                    //Loading Country
                    sb.Append("<div class=\"comp_details1\"><p><strong>" + DAO.TextBaseFactory.loadText("country_loading", this.LangId) + "</strong></p>");
                    if (LoadLoadingUnloadingData.TryGetValue("loading_codes", out loading_codes) &&
                        LoadLoadingUnloadingData.TryGetValue("loading_countries", out loading_countries))
                    {
                        int count = loading_codes.Count;
                        if (count == 1)
                        {
                            sb.Append("<p style='text-align:left;padding-left:10px'><img width=\"20\" title='" + loading_countries[0].ToString() + "' height=\"12\" src=\"images/countries/" + loading_codes[0].ToString() + "-t.gif\" />&nbsp;&nbsp;");
                            if (loading_countries[0].ToString().Length < 12)
                            {
                                sb.Append(loading_codes[0].ToString() + " - " + loading_countries[0].ToString());
                            }
                            else
                            {
                                sb.Append(loading_codes[0].ToString() + " - " + loading_countries[0].ToString().Substring(0,12)+"...");
                            }
                        }
                        else
                        {
                            sb.Append("<p>");
                            for (int i = 0; i < count; i++)
                            {
                                sb.Append("<img width=\"20\" height=\"12\" title='" + loading_countries[i].ToString() + "' src=\"images/countries/" + loading_codes[i].ToString() + "-t.gif\" />&nbsp;");
                            }
                        }
                    }
                    sb.Append("</p></div>");
                    //Unloading Country
                    sb.Append("<div class=\"comp_details1\"><p><strong>" + DAO.TextBaseFactory.loadText("country_unloading", this.LangId) + "</strong></p>");
                    if (LoadLoadingUnloadingData.TryGetValue("unloading_codes", out unloading_codes) &&
                        LoadLoadingUnloadingData.TryGetValue("unloading_countries", out unloading_countries))
                    {
                        int count = unloading_countries.Count;
                        if (count == 1)
                        {
                            sb.Append("<p style='text-align:left;padding-left:10px'><img width=\"20\" title='" + unloading_countries[0].ToString() + "' height=\"12\" src=\"images/countries/" + unloading_codes[0].ToString() + "-t.gif\" />&nbsp;&nbsp;");
                            if (unloading_countries[0].ToString().Length < 12)
                            {
                                if (unloading_codes[0].ToString()!="")
                                    sb.Append(unloading_codes[0].ToString() + " - " + unloading_countries[0].ToString());
                                else
                                    sb.Append("N/A");
                            }
                            else
                            {
                                sb.Append(unloading_codes[0].ToString() + " - " + unloading_countries[0].ToString().Substring(0, 12) + "...");
                            }
                        }
                        else
                        {
                            sb.Append("<p>");
                            for (int i = 0; i < unloading_countries.Count; i++)
                            {
                                sb.Append("<img width=\"20\" title='" + unloading_countries[i].ToString() + "' height=\"12\" src=\"images/countries/" + unloading_codes[i].ToString() + "-t.gif\" />&nbsp;");
                            }
                        }
                    }
                    sb.Append("</p></div>");

                    string date_1_text = DAO.TextBaseFactory.loadText("loading", this.LangId);
                    string date_2_text = DAO.TextBaseFactory.loadText("unloading", this.LangId);
                    if (tender_app)
                    {
                        date_1_text = DAO.TextBaseFactory.loadText("openingdate", this.LangId);
                        date_2_text = DAO.TextBaseFactory.loadText("closingdate", this.LangId);
                    }
                    
                    //Loading Unloading Dates
                    sb.Append("<div class=\"comp_details2\"><p><strong>" + date_1_text + "</strong></p><p>" + loading_date + "</p></div>");
                    sb.Append("<div class=\"comp_details2\"><p><strong>" + date_2_text + "</strong></p><p>" + unloading_date + "</p></div>");
                    sb.Append("<div class=\"comp_details1\"><p><strong>" + DAO.TextBaseFactory.loadText(univer_type, this.LangId) + "</strong></p>");
                    sb.Append("<p>" + (dr["univer_name"].ToString() != "" ? dr["univer_name"].ToString() : "N/A") +
                        /*"</p></div><div class=\"comp_details2\"><p><strong>" + DAO.TextBaseFactory.loadText("packing_type", this.LangId) + "</strong></p>");
                    sb.Append("<p>" + (dr["packing_name"].ToString() != "" ? dr["packing_name"].ToString() : "N/A") +*/ "</p></div>");
                    
                    sb.Append("<div class=\"compMore\"><div class=\"viewInfoLink\"><a href=\"ApplicationDetails.aspx?id=" + dr["id"].ToString() + "\" class=\"viewInfoLink\" >" + 
                        DAO.TextBaseFactory.loadText("comp_info_det", this.LangId) + "</a></div>");
                    sb.Append("</div></div></div>");
                    phCompany.Controls.Add(new LiteralControl(sb.ToString()));
                }
                dr.Close();//Close the Reader
                CreatePaging(page, start, limit, page_size, (int)total_records);//Create Paging
            }
        }
    }
    #endregion

    #region LoadUniversalType
    private string LoadUniversalType(long id)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            string cmd1 = "";
            Int64 langId = DAO.Connection.DEFAULT_LANGUAGE;
            if (Session["language"] != null)
            {
                Int64.TryParse(Session["language"].ToString(), out langId);
            }
            if (langId != DAO.Connection.DEFAULT_LANGUAGE)
                cmd1 = "SELECT id, name FROM dbo.Fn_GetUniversalTypeByLang(@id,@language_id)";
            else
                cmd1 = "SELECT id, name FROM dbo.Fn_GetUniversalTypeById(@id,@language_id)";
            using (SqlCommand cmd = new SqlCommand(cmd1, conn))
            {
                cmd.Parameters.Add("id", id);
                cmd.Parameters.Add("language_id", langId);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    return dr["name"].ToString();
                }
                dr.Close();
                return "";
            }
        }
    }
    #endregion
    
    #region LoadLoadingUnloadingInfo
    private Dictionary<string, ArrayList> LoadLoadingUnloadingInfo(long app_id)
    {
        Dictionary<string, ArrayList> LoadingUnloadingInfo = new Dictionary<string, ArrayList>();
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Fn_getLoadingUnloadingByAppId(@app_id)", conn))
            {
                cmd.Parameters.Add("app_id", app_id);
                SqlDataReader dr = cmd.ExecuteReader();
                ArrayList loading_codes = new ArrayList();
                ArrayList loading_countries = new ArrayList();
                ArrayList unloading_countries = new ArrayList();
                ArrayList loading_dates = new ArrayList();
                ArrayList unloading_codes = new ArrayList();
                ArrayList unloading_dates = new ArrayList();
                while (dr.Read())
                {
                    loading_codes.Add(dr["loading_country_code"].ToString());
                    loading_dates.Add(dr["loading_date"].ToString());
                    unloading_codes.Add(dr["unloading_country_code"].ToString());
                    unloading_dates.Add(dr["unloading_date"].ToString());
                    loading_countries.Add(dr["loading_country_name"].ToString());
                    unloading_countries.Add(dr["unloading_country_name"].ToString());
                    
                }
                LoadingUnloadingInfo.Add("loading_codes", loading_codes);
                LoadingUnloadingInfo.Add("loading_dates", loading_dates);
                LoadingUnloadingInfo.Add("unloading_codes", unloading_codes);
                LoadingUnloadingInfo.Add("unloading_dates", unloading_dates);
                LoadingUnloadingInfo.Add("loading_countries", loading_countries);
                LoadingUnloadingInfo.Add("unloading_countries", unloading_countries);

            }
        }
        return LoadingUnloadingInfo;
    }
    #endregion

    #region CreatePaging
    private void CreatePaging(int page, int start, int limit, int page_size, int records)
    {
        if(limit>records)
        {
            limit = records;
        }
        double pages = Math.Ceiling(Convert.ToDouble(records) / page_size);
        HyperLink hl;
        int _pages = (int)pages;
        for (int i = 1; i <= _pages; i++)
        {
            hl = new HyperLink();
            hl.Text = i.ToString();
            hl.CssClass = "lblInfo";
            hl.NavigateUrl = "~/" + filename + "?page=" + i.ToString();
            if (i != 1 && i != (_pages + 1))
                phNav.Controls.Add(new LiteralControl("<span>&nbsp;|&nbsp;</span>"));
            phNav.Controls.Add(hl);
            if ((i == pages) && page != _pages)
            {
                phNav.Controls.Add(new LiteralControl("<span>&nbsp;|&nbsp;</span>"));
                hl = new HyperLink();
                hl.Text = DAO.TextBaseFactory.loadText("next", this.LangId);
                hl.CssClass = "lblInfo";
                hl.NavigateUrl = "~/" + filename + "?page=" + (page + 1).ToString();
                phNav.Controls.Add(hl);
            }
        }
        if (records == 0)
        {
            lblInfo.Text = DAO.TextBaseFactory.loadText("no_records", this.LangId);
        }
        else
        {
            lblInfo.Text = DAO.TextBaseFactory.loadText("showing", this.LangId) + " " + start + "-" + limit + " " + DAO.TextBaseFactory.loadText("of", this.LangId) + " " + records + " " + DAO.TextBaseFactory.loadText("total_res", this.LangId);
        }
    }
    #endregion

    #region InitializeUI
    protected override void InitializeUI()
    {
        long langId = DAO.Connection.DEFAULT_LANGUAGE;
        if (Session["language"] != null)
            long.TryParse(Session["language"].ToString(), out langId);

        this.LangId = Convert.ToInt32(langId);
    }
    #endregion

    protected void ddl_Sortby_SelectedIndexChanged(object sender, EventArgs e)
    {
        Int32 sort = 0;
        Int32.TryParse(ddl_Sortby.SelectedValue, out sort);

        LoadApplications(sort);
    }
}
